package com.cpi.cornpp.grinddistribution.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.grinddistribution.dto.GrindDistributionDTO;
import com.cpi.cornpp.grinddistribution.exception.GrindDistributionException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;
/**
 * 
 * @description: This Class is used to handle the Grind Distribution Data Access
 * @author Edgar Joao
 */
public class GrindDistributionDAO extends DBDAO {
	
	private static final String SELECT_GRIND_DISTRIBUTION_BY_FILTER = "SELECT ct.corn_type_id, code,description, corn_origin_id, grind_distribution_setting_versions_id, inventory, delivered FROM corns_types ct left join grinds_distributions_settings_versions s on s.corn_type_id = ct.corn_type_id  and s.version_id = ?";
	private static final String INSERT_GRIND_DISTRIBUTION = "INSERT INTO grinds_distributions_settings_versions (corn_type_id, version_id, inventory, delivered) VALUES (?,?,?,?)";
	private static final String UPDATE_GRIND_DISTRIBUTION = "UPDATE grinds_distributions_settings_versions SET inventory = ?, delivered = ? WHERE grind_distribution_setting_versions_id = ?";
	private static final String SELECT_BEFORE_INSERT_GRIND_DISTRIBUTION_FORECAST = "SELECT count(*) FROM grinds_distributions_settings_versions WHERE corn_type_id = ? AND version_id = ?";
	
	public List getGrindDistributionList(int versionId) throws GrindDistributionException{
		List listOfGrindDistribution = null;
		GrindDistributionDTO dto = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;		
		try{
			listOfGrindDistribution = new ArrayList(0);
			preparedStatement = prepareSQL(SELECT_GRIND_DISTRIBUTION_BY_FILTER, dsName);
			preparedStatement.setInt(1, versionId);
			rs = executeQuery(preparedStatement);
			while(rs.next()){
				dto = new GrindDistributionDTO();
				dto.setCornTypeId(new Integer(rs.getInt(1)));
				dto.setCode(rs.getString(2));
				dto.setDescription(rs.getString(3));
				dto.setCornOrigin(rs.getString(4));
				dto.setGrindDistributionSettingVersionsId(new Integer(rs.getInt(5)));
				dto.setInventory(new Integer(rs.getInt(6)));
				dto.setDelivered(new Integer(rs.getInt(7)));
				dto.setVersionId(new Integer(versionId));
				listOfGrindDistribution.add(dto);				
			}
		}catch (PoolException poolException){
			GrindDistributionException grindDistributionException = null;
			poolException.printStackTrace(System.out);
			grindDistributionException = new GrindDistributionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw grindDistributionException;	
		} catch (SQLException sqlException) {
			GrindDistributionException grindDistributionException = null;
			sqlException.printStackTrace(System.out);
			grindDistributionException = new GrindDistributionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw grindDistributionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfGrindDistribution;
	}
	/**
	 * Insert Grind Distributions
	 * @param dto
	 * @throws GrindDistributionException
	 */
	public void insertGrindDistribution(GrindDistributionDTO dto) throws GrindDistributionException{
		PreparedStatement preparedStatement = null;				
		try{										
			preparedStatement = prepareSQL(INSERT_GRIND_DISTRIBUTION, dsName);		
			preparedStatement.setInt(1, dto.getCornTypeId().intValue());
			preparedStatement.setInt(2, dto.getVersionId().intValue());
			preparedStatement.setInt(3, dto.getInventory().intValue());
			preparedStatement.setInt(4, dto.getDelivered().intValue());			
			executeInsert(preparedStatement);						
		}catch (PoolException poolException){
			GrindDistributionException grindDistributionException = null;
			poolException.printStackTrace(System.out);
			grindDistributionException = new GrindDistributionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw grindDistributionException;	
		} catch (SQLException sqlException) {
			GrindDistributionException grindDistributionException = null;
			sqlException.printStackTrace(System.out);
			grindDistributionException = new GrindDistributionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_INSERT);
			throw grindDistributionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}	
	/**
	 * Update Grind Distribution
	 * @param dto
	 * @throws GrindDistributionException
	 */
	public void updateGrindDistribution(GrindDistributionDTO dto) throws GrindDistributionException{
		PreparedStatement preparedStatement = null;				
		try{										
			preparedStatement = prepareSQL(UPDATE_GRIND_DISTRIBUTION, dsName);				
			preparedStatement.setInt(1, dto.getInventory().intValue());
			preparedStatement.setInt(2, dto.getDelivered().intValue());	
			preparedStatement.setInt(3, dto.getGrindDistributionSettingVersionsId().intValue());
			executeInsert(preparedStatement);						
		}catch (PoolException poolException){
			GrindDistributionException grindDistributionException = null;
			poolException.printStackTrace(System.out);
			grindDistributionException = new GrindDistributionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw grindDistributionException;	
		} catch (SQLException sqlException) {
			GrindDistributionException grindDistributionException = null;
			sqlException.printStackTrace(System.out);
			grindDistributionException = new GrindDistributionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw grindDistributionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
	/**
	 * Verify if current Grind Distribution exist
	 * @param dto
	 * @return
	 * @throws GrindDistributionException
	 */
	public boolean existGrindDistribution(GrindDistributionDTO dto) throws GrindDistributionException{
		PreparedStatement preparedStatement =  null;
		boolean result = false;		
		try {
			preparedStatement =  prepareSQL(SELECT_BEFORE_INSERT_GRIND_DISTRIBUTION_FORECAST, dsName);
			preparedStatement.setInt(1, dto.getCornTypeId().intValue());
			preparedStatement.setInt(2, dto.getVersionId().intValue());
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {
				result=rst.getBoolean(1);
			}
		}catch (PoolException poolException){
			GrindDistributionException grindDistributionException = null;
			poolException.printStackTrace(System.out);
			grindDistributionException = new GrindDistributionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw grindDistributionException;	
		} catch (SQLException sqlException) {
			GrindDistributionException grindDistributionException = null;
			sqlException.printStackTrace(System.out);
			grindDistributionException = new GrindDistributionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_SELECT);
			throw grindDistributionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return result;		
	}
}
